package cn.com.codingce.student.dao.impl;

import cn.com.codingce.student.dao.ITeacherDao;
import cn.com.codingce.student.entity.Login;
import cn.com.codingce.student.entity.Student;
import cn.com.codingce.student.util.DBUtil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * DAO层
 *
 * @author williamma
 */
public class TeacherDaoImpl implements ITeacherDao {

    public static void main(String[] args) {
        ITeacherDao tea = new TeacherDaoImpl();
//		List<Login> teal = tea.queryAllTeachers();
//		System.out.println(teal);
//		Login login = new Login("123","456");
//		System.out.println(tea.login(login));
        Login login = new Login(12, "哈哈", "123");
        tea.addTeacher(login);


    }

    /**
     * @param sno
     * @param student
     * @return
     */
    public boolean updateStudentBySno(int sno, Student student) {
        String sql = "SELECT * FROM login where uname = ? and upwd = ?";
        Object[] params = {student.getSno(), student.getSname(), student.getSage(), student.getSaddress(), sno};
        return DBUtil.executeUpdate(sql, params);
    }

    /**
     * @param login
     * @return
     */
    @Override
    public int login(Login login) {
        // boolean fag = false;
        int flag = -1; // -1系统异常 0用户名密码错误 1正确
        int result = -1;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            String URL = "jdbc:mysql://localhost:3306/codingce-student?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai";

            conn = DriverManager.getConnection(URL, "root", "1234567890");// userName是你数据库的用户名如sa,
            // 执行操作
            String sql = "SELECT * FROM login where uname = ? and upwd = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, login.getUname());
            pstmt.setString(2, login.getUpwd());
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            if (result > 0) {
                return 1;
            } else {
                return 0;
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return -1;

        } catch (SQLException e) {
            e.printStackTrace();
            return -1;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (conn != null)
                    conn.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }


    /**
     * 查询总数据数
     *
     * @return
     */
    @Override
	public int getTotalCount() {
        String sql = "select count(1) from login";
        return DBUtil.getTotalCount(sql);
    }

    /**
     * 查询全部老师
     *
     * @return
     */
    @Override
	public List<Login> queryAllTeachers() {
        List<Login> teachers = new ArrayList<>();
        Login teacher = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM login";
            rs = DBUtil.executeQuery(sql, null);

            // rs = pstmt.executeQuery(); 通过上一个查
            while (rs.next()) {

                int id = rs.getInt("id");
                String uname = rs.getString("uname");

                String upwd = rs.getString("upwd");
                teacher = new Login(id, uname, upwd);
                teachers.add(teacher);
            }
            return teachers;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            DBUtil.closeAll(rs, pstmt, DBUtil.connection);
        }
    }

    /**
     * 根据工号删除老师
     *
     * @param id
     * @return
     */
    @Override
    public boolean deleteTeacherById(int id) {
        String sql = "delete from login where id = ?";
        Object[] params = {id};
        return DBUtil.executeUpdate(sql, params);
    }

    /**
     * @param id
     * @return
     */
    @Override
    public Login queryLoginById(int id) {
        Login tea = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            String URL = "jdbc:mysql://localhost:3306/codingce-student?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";

            conn = DriverManager.getConnection(URL, "root", "1234567890");// userName是你数据库的用户名如sa,
            // 执行操作
            String sql = "SELECT * FROM login where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int idtwo = rs.getInt("id");
                String uname = rs.getString("uname");
                String upwd = rs.getString("upwd");
                tea = new Login(idtwo, uname, upwd);
            }
            return tea;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (pstmt != null)
                    pstmt.close();
                if (conn != null)
                    conn.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }


    /**
     * 修改教师
     *
     * @param id
     * @param login
     * @return
     */
    @Override
    public boolean updateTeacherById(int id, Login login) {
        String sql = "update login set id=?,uname=?,upwd=? where id = ?";
        Object[] params = {login.getId(), login.getUname(), login.getUpwd(), id};
        return DBUtil.executeUpdate(sql, params);
    }

    /**
     *
     *
     * @param tea
     * @return
     */
    @Override
    public boolean addTeacher(Login tea) {
        String sql = "INSERT INTO login values(?,?,?)";
        Object[] params = {tea.getId(), tea.getUname(), tea.getUpwd()};
        return DBUtil.executeUpdate(sql, params);
    }
}
